package com.yuanqi.repository;

import com.yuanqi.domain.RetailSalesOrder;
import com.yuanqi.domain.CustomerType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 零售销售订单数据访问层
 */
@Repository
public interface RetailSalesOrderRepository extends JpaRepository<RetailSalesOrder, Long> {
    
    /**
     * 根据订单编码查找
     */
    Optional<RetailSalesOrder> findByOrderCode(String orderCode);
    
    /**
     * 根据客户ID查找订单
     */
    List<RetailSalesOrder> findByCustomerIdOrderByOrderDateDesc(Long customerId);
    
    /**
     * 根据客户姓名查找订单
     */
    List<RetailSalesOrder> findByCustomerNameContainingOrderByOrderDateDesc(String customerName);
    
    /**
     * 根据客户电话查找订单
     */
    List<RetailSalesOrder> findByCustomerPhoneOrderByOrderDateDesc(String customerPhone);
    
    /**
     * 根据订单状态查找
     */
    List<RetailSalesOrder> findByOrderStatusOrderByOrderDateDesc(String orderStatus);
    
    /**
     * 根据客户类型查找订单
     */
    List<RetailSalesOrder> findByCustomerTypeOrderByOrderDateDesc(CustomerType customerType);
    
    /**
     * 根据日期范围查找订单
     */
    @Query("SELECT rso FROM RetailSalesOrder rso WHERE rso.orderDate BETWEEN :startDate AND :endDate ORDER BY rso.orderDate DESC")
    List<RetailSalesOrder> findByOrderDateBetween(@Param("startDate") LocalDateTime startDate, 
                                                  @Param("endDate") LocalDateTime endDate);
    
    /**
     * 根据客户和日期范围查找订单
     */
    @Query("SELECT rso FROM RetailSalesOrder rso WHERE rso.customerId = :customerId AND rso.orderDate BETWEEN :startDate AND :endDate ORDER BY rso.orderDate DESC")
    List<RetailSalesOrder> findByCustomerIdAndOrderDateBetween(@Param("customerId") Long customerId,
                                                               @Param("startDate") LocalDateTime startDate,
                                                               @Param("endDate") LocalDateTime endDate);
    
    /**
     * 统计今日订单数量
     */
    @Query("SELECT COUNT(rso) FROM RetailSalesOrder rso WHERE DATE(rso.orderDate) = CURRENT_DATE")
    Long countTodayOrders();
    
    /**
     * 统计本月订单数量
     */
    @Query("SELECT COUNT(rso) FROM RetailSalesOrder rso WHERE YEAR(rso.orderDate) = YEAR(CURRENT_DATE) AND MONTH(rso.orderDate) = MONTH(CURRENT_DATE)")
    Long countMonthOrders();
    
    /**
     * 统计今日销售额
     */
    @Query("SELECT COALESCE(SUM(rso.finalAmount), 0) FROM RetailSalesOrder rso WHERE DATE(rso.orderDate) = CURRENT_DATE")
    Double sumTodaySales();
    
    /**
     * 统计本月销售额
     */
    @Query("SELECT COALESCE(SUM(rso.finalAmount), 0) FROM RetailSalesOrder rso WHERE YEAR(rso.orderDate) = YEAR(CURRENT_DATE) AND MONTH(rso.orderDate) = MONTH(CURRENT_DATE)")
    Double sumMonthSales();
    
    /**
     * 查找待处理的订单
     */
    @Query("SELECT rso FROM RetailSalesOrder rso WHERE rso.orderStatus = 'Pending' ORDER BY rso.orderDate ASC")
    List<RetailSalesOrder> findPendingOrders();
    
    /**
     * 查找处理中的订单
     */
    @Query("SELECT rso FROM RetailSalesOrder rso WHERE rso.orderStatus = 'Processing' ORDER BY rso.orderDate ASC")
    List<RetailSalesOrder> findProcessingOrders();
    
    /**
     * 查找已发货的订单
     */
    @Query("SELECT rso FROM RetailSalesOrder rso WHERE rso.orderStatus = 'Shipped' ORDER BY rso.orderDate ASC")
    List<RetailSalesOrder> findShippedOrders();
    
    /**
     * 统计商品销量
     */
    @Query("SELECT COALESCE(SUM(roi.quantity), 0) FROM RetailSalesOrder rso JOIN rso.orderItems roi WHERE roi.productId = :productId")
    Long countProductSales(@Param("productId") Long productId);
    
    /**
     * 统计商品销售额
     */
    @Query("SELECT COALESCE(SUM(roi.itemTotalAmount), 0) FROM RetailSalesOrder rso JOIN rso.orderItems roi WHERE roi.productId = :productId")
    Double sumProductRevenue(@Param("productId") Long productId);
    
    /**
     * 统计商品本月销量
     */
    @Query("SELECT COALESCE(SUM(roi.quantity), 0) FROM RetailSalesOrder rso JOIN rso.orderItems roi WHERE roi.productId = :productId AND YEAR(rso.orderDate) = YEAR(CURRENT_DATE) AND MONTH(rso.orderDate) = MONTH(CURRENT_DATE)")
    Long countProductSalesThisMonth(@Param("productId") Long productId);
    
    /**
     * 统计商品本月销售额
     */
    @Query("SELECT COALESCE(SUM(roi.itemTotalAmount), 0) FROM RetailSalesOrder rso JOIN rso.orderItems roi WHERE roi.productId = :productId AND YEAR(rso.orderDate) = YEAR(CURRENT_DATE) AND MONTH(rso.orderDate) = MONTH(CURRENT_DATE)")
    Double sumProductRevenueThisMonth(@Param("productId") Long productId);
}
